using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Reflection;

namespace SqlTools.UnitTesting
{
	public class SqlTestHelper
	{
		protected string _connectionString;
		protected string _connectionStringName;

		protected List<string> _scriptDelimiters;

		public SqlTestHelper(string connectionStringName)
		{
			_connectionStringName = connectionStringName;
			LoadConnectionString(_connectionStringName);

			_scriptDelimiters = new List<string>();
			_scriptDelimiters.Add("GO");
		}

		public void LoadConnectionString(string connName)
		{
			if ( ConfigurationManager.ConnectionStrings[connName] != null )
			{
				_connectionString = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
			}
		}

		public void ExecuteScript(Type type, string resourceName)
		{
			ExecuteScript(type, resourceName, true);
		}

		public void ExecuteScript(Type type, string resourceName, bool transactional)
		{
			Assembly assembly = type.Assembly;

			AssemblyName assemblyName = new AssemblyName(assembly.FullName);

			string resource = assemblyName.Name + "." + resourceName;

			Stream stream = assembly.GetManifestResourceStream(resource);

			ExecuteScript(stream, transactional);
		}

		public void ExecuteScript(Stream stream)
		{
			ExecuteScript(stream, true);
		}

		public void ExecuteScript(Stream stream, bool transactional)
		{
			StreamReader sr = new StreamReader(stream);

			string sqlScript = sr.ReadToEnd();

			ExecuteScript(sqlScript, transactional);
		}

		public void ExecuteScript(string sqlScript)
		{
			ExecuteScript(sqlScript, true);
		}

		public void ExecuteScript(string sqlScript, bool transactional)
		{
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				// we need to parse out the script delimiters
				string[] scripts = sqlScript.Split(_scriptDelimiters.ToArray(), StringSplitOptions.RemoveEmptyEntries);

				// if transactional, then create a transaction
				SqlTransaction trans = null;

				try
				{
					conn.Open();

					if ( transactional )
						trans = conn.BeginTransaction();

					foreach ( string script in scripts )
					{
						SqlCommand cmd = new SqlCommand(script, conn, trans);

						cmd.ExecuteNonQuery();
					}

					if ( transactional && trans != null )
					{
						trans.Commit();
					}

					conn.Close();
				}
				catch ( Exception )
				{
					if ( transactional && trans != null )
					{
						trans.Rollback();
					}
					throw;
				}
			}
		}


		public SqlDataReader ExecuteReader(string query)
		{
			return ExecuteReader(query, null);
		}


		public SqlDataReader ExecuteReader(string query, SqlTransaction trans)
		{
			bool destroyConn = false;

			SqlConnection conn = new SqlConnection(_connectionString);

			SqlCommand cmd = new SqlCommand(query, conn);
			cmd.CommandType = CommandType.Text;
			cmd.Transaction = trans;

			conn.Open();

			SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

			return sdr;
		}

		public int GetRowCount(string tablename)
		{
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				conn.Open();

				string sql = string.Format("SELECT COUNT(*) FROM [{0}]", tablename);

				SqlCommand cmd = new SqlCommand(sql, conn);

				int rows = (int)cmd.ExecuteScalar();

				conn.Close();

				return rows;
			}
		}

		public List<string> ScriptDelimiters
		{
			get { return _scriptDelimiters; }
		}

		public string ConnectionString
		{
			get { return _connectionString; }
		}

		public string ConnectionStringName
		{
			get { return _connectionStringName; }
		}
	}
}
